package com.dao.user;

import com.model.po.User;
import com.model.po.UserGroup;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface UserMapper {

    /*
    新增一条用户数据
     */
    @Insert("insert into bi_users(bu_id, bu_name, BU_POST, USER_NAME, PASS_WORD, BU_DEPARTMENT, CREATE_DATE) values" +
            "(#{id}, #{name}, #{post}, #{userName}, #{passWord}, #{department}, to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT bi_users_sequence.nextval from dual",keyColumn = "bu_id")
    void addUser(User user);

    /*
    更新用户数据
     */
    @Update("<script>" +
            "UPDATE bi_users set " +
            "USER_NAME = #{userName}" +
            "<if test=\"passWord != null\"> , PASS_WORD = #{passWord} </if>" +
            " <if test=\"name != null\"> , bu_name = #{name} </if>" +
            " <if test=\"post != null\"> , BU_POST = #{post} </if>" +
            " <if test=\"department != null\"> , BU_DEPARTMENT = #{department} </if>" +
            " <if test=\"role != null\"> , BU_ROLE = #{role} </if>" +
            " ,updata_date = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss')" +
            "  where bu_id = #{id}" +
            " </script>")
    void updateUser(User user);

    /*
    查询id
     */
    @Select("select bu_id as id from bi_users where user_name = #{userName}")
    int getUserId(String userName);

    /*
    查询Role
     */
    @Select("select bu_role as id from bi_users where bu_id = #{id}")
    String getRole(int id);

    /*
    查询uas用户信息
     */
    @Select("select EM_DEFAULTHSNAME as department, EM_DEPART as post, EM_NAME as name, em_password as passWord," +
            " em_mobile as phone, em_code as userName from EMPLOYEE where ${column} = #{userName}")
    User getUserMess(@Param("userName") String userName, @Param("column") String column);

    /*
    获取用户信息
     */
    @Select("select BU_ID as id, BU_NAME as name, BU_DEPARTMENT as department, BU_POST as post, BU_ROLE as role," +
            " user_name as userName, pass_word as passWord " +
            "from bi_users where user_name = #{userName}")
    User getLogin(String userName);

    /*
    查询bi系统是否存在该用户
     */
    @Select("select bu_name as name from bi_users where user_name = #{userName}")
    String getUserName(String userName);

    /*
    通过ID获取名称
     */
    @Select("select bu_name from bi_users where bu_id = #{id}")
    String getName(int id);

    /*
    获取用户列表
     */
    @Select("select BU_ID as id, BU_NAME as name, BU_DEPARTMENT as department, BU_POST as post, BU_ROLE as role," +
            "user_name as userName, pass_word as passWord, create_date as createDate " +
            "from bi_users")
    List<User> getUserList();

    /*
    匹配用户20个
     */
    @Select("select BU_ID as id, BU_NAME as name, BU_DEPARTMENT as department, BU_POST as post, BU_ROLE as role " +
            "from bi_users where bu_name ${value} and rownum<21")
    List<User> getNameList(String value);

    /*
    更新token
     */
    @Update("update bi_users set user_token = #{token} where bu_id = #{id}")
    void updateToken(@Param("token") String token, @Param("id") int id);

    /*
    创建用户组
     */
    @Insert("insert into bi_user_groups(bg_id, bg_name, bg_note, create_date) values(#{id}, #{userGroupName}, #{userGroupNote}, " +
            "to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))")
    @SelectKey(before=true,keyProperty="id",resultType=Integer.class,statement="SELECT BI_USER_GROUPS_SEQUENCE.nextval from dual",keyColumn = "bg_id")
    void insUserGroup(UserGroup userGroup);

    /*
    更新用户组
     */
    @Update("<script>" +
            "UPDATE bi_user_groups set "  +
            "bg_name = #{userGroupName}" +
            "<if test=\"userGroupNote != null\"> , bg_note = #{userGroupNote} </if>" +
            ",UPDATA_DATE = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') " +
            "where bg_id = #{id}" +
            "</script>")
    void updateUserGroup(UserGroup userGroup);

    /*
    查询用户组
     */
    @Select("select bg_id as id, bg_name as userGroupName, bg_note as userGroupNote, create_date as createDate, " +
            "updata_date as updateDate from bi_user_groups ")
    List<UserGroup> getUserGroup();

    /*
    删除用户组
     */
    @Delete("<script>" +
            "delete from bi_user_groups where bg_id in " +
            "("+
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}"+
            "</foreach>" +
            ")"+
            "</script>")
    void delUserGroup(List<Integer> idList);

    /*
    添加用户到用户组
     */
    @Insert("insert into bi_user_rel_groups(br_id, br_user_id, br_user_group) values(#{id}, #{userId}, #{userGroupId})")
    @SelectKey(before=true,keyProperty="id",resultType=Integer.class,statement="SELECT BI_USER_REL_GROUPS_SEQUENCE.nextval from dual",keyColumn = "br_id")
//    void setUserInto(@Param("userId") int userId, @Param("userGroupId") int userGroupId);
    void setUserInto(@Param("userId") int userId, @Param("userGroupId") int userGroupId);

    /*
    查询用户组是否有该用户
     */
    @Select("select count(*) from bi_user_rel_groups where br_user_id = #{userId} and br_user_group = #{userGroupId} ")
    int getUserCountInGroup(@Param("userId") int userId, @Param("userGroupId") int userGroupId);

    /*
    查询用户组下的用户信息
     */
    @Select("select bu_id as id, bu_name as name, bu_department as department, bu_post as post, bu_role as role " +
            " from bi_users,bi_user_groups,bi_user_rel_groups where bi_users.bu_id=bi_user_rel_groups.br_user_id and " +
            " bi_user_rel_groups.br_user_group=bi_user_groups.bg_id and bi_user_groups.bg_id = #{id}")
    List<User> getGroupUserList(int id);

    /*
    删除用户组下的用户
     */
    @Delete("delete from bi_user_rel_groups where br_user_id = #{userId} and br_user_group = #{userGroupId} ")
    void delUserInGroup(@Param("userId") int userId, @Param("userGroupId") int userGroupId);

}
